using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
 

namespace UploadData
{
    class ExcelDataProvider
    {
        string errorMessage = "";
        internal string ErrorMessage
        {             
            get { return errorMessage; }
        }
        string providerName = "Microsoft.Jet.OLEDB.4.0";
        internal string Provider
        {
            set{providerName = value;}
            get{return providerName;}
        }
        string excelPathFile = "";
        internal string ExcelPathFile
        {
            set { excelPathFile = value; }
            get { return excelPathFile; } 
        }
        string excelVersion = "Excel 8.0";
        internal string ExcelVersion
        {
            set { excelVersion = value; }
            get { return excelVersion; }
        }
        
        internal DataTable GetDataFromExcel(string sheetName)
        {
            DataTable excelTable = new DataTable();
            string connectionString =
                @"Provider=" + providerName + 
                ";Data Source=" + excelPathFile
                + @";Extended Properties=" + 
                Convert.ToChar(34).ToString() 
                + excelVersion + 
                ";HDR=Yes;IMEX=1" + 
                Convert.ToChar(34).ToString();
            try
            {
                using (OleDbConnection oleDbConnection = 
                new OleDbConnection(connectionString))
                {                    
                    OleDbCommand oleDbCommand = new 
                    OleDbCommand("select * from [" 
                    + sheetName + "$]", oleDbConnection);
                    OleDbDataAdapter oleDbDataAdapter =
                    new OleDbDataAdapter(oleDbCommand);
                    
                    oleDbDataAdapter.Fill(excelTable);
                }
            }
            catch(Exception ex)
            {
                errorMessage = ex.Message;
                excelTable = null;
            }
            return excelTable;
        }

        internal DataTable GetDataFromExcel(
        string sheetName, string startCell, 
        string endCell)
        {
            DataTable excelTable = new DataTable();
            string connectionString =
                @"Provider=" + providerName +
                ";Data Source=" + excelPathFile
                + @";Extended Properties=" +
                Convert.ToChar(34).ToString()
                + excelVersion +
                ";HDR=Yes;IMEX=1" +
                Convert.ToChar(34).ToString();
            try
            {
                using (OleDbConnection oleDbConnection =
                new OleDbConnection(connectionString))
                {
                    OleDbCommand oleDbCommand = new
                    OleDbCommand("select * from ["
                    + sheetName + "$" + startCell
                        + ":" + endCell + "]", 
                        oleDbConnection);
                    OleDbDataAdapter oleDbDataAdapter =
                    new OleDbDataAdapter(oleDbCommand);

                    oleDbDataAdapter.Fill(excelTable);
                }
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
                excelTable = null;
            }
            return excelTable;
        }
        
        internal DataTable GetSheetName()
        {
            DataTable excelTable = new DataTable();
            string connectionString =
                @"Provider=" + providerName +
                ";Data Source=" + excelPathFile
                + @";Extended Properties=" +
                Convert.ToChar(34).ToString()
                + excelVersion +
                ";HDR=Yes;IMEX=2" +
                Convert.ToChar(34).ToString();
              try
            {
                 
                using (OleDbConnection oleDbConnection =
                new OleDbConnection(connectionString))
                {
                    oleDbConnection.Open();
                    excelTable = 
                    oleDbConnection.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables, null);                     
                }
            }
            catch(Exception exx)
            {
                excelTable = null;
                errorMessage = exx.Message;
            }
            return excelTable;
        }
    }
}
